﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;
using static System.Windows.Forms.VisualStyles.VisualStyleElement;
using System.IO;
using System.Diagnostics;

namespace WindowsFormsApp10
{
    public partial class Form1 : Form
    {
        private string connectionString = "Data Source=mydatabase.db;Version=3;";
        private SELECT chosed;
        public Form1()
        {
            InitializeComponent();
            /*creatTables();
            School school1 = new School(1, "School A");
            School school2 = new School(2, "School B");

            Class class1A = new Class(101, "Class A1",1);
            Class class1B = new Class(102, "Class A2",1);
            Class class2A = new Class(201, "Class B1",2);
            Class class2B = new Class(202, "Class B2",2);

            Student student1 = new Student(1, "Alice", 18, 101);
            Student student2 = new Student(2, "Bob", 19, 101);   
            Student student3 = new Student(3, "Charlie", 20, 101); 

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    // 插入学校数据
                    InsertSchool(connection, school1);
                    InsertSchool(connection, school2);

                    // 插入班级数据
                    InsertClass(connection, class1A);
                    InsertClass(connection, class1B);
                    InsertClass(connection, class2A);
                    InsertClass(connection, class2B);

                    InsertStudent(connection, student1);
                    InsertStudent(connection, student2);
                    InsertStudent(connection, student3);

                    transaction.Commit();
                }

                connection.Close();
            }*/
            

            FillTreeView();
            // 添加TreeView的点击事件处理程序
            treeView1.AfterSelect += TreeView1_AfterSelect;

            listView1.View = View.Details;

        }
        public static void writeLog(string message)
        {
            try
            {
                using (FileStream fileStream = new FileStream("log.txt", FileMode.Append, FileAccess.Write, FileShare.ReadWrite))
                {
                    using (StreamWriter writer = new StreamWriter(fileStream))
                    {
                    
                        writer.WriteLine(message);
                    }
                }

                Console.WriteLine("文件写入成功！");
            }
            catch (Exception ex)
            {

            }
        }
        private void creatTables()
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                // 创建学校表
                string createSchoolTable = "CREATE TABLE School (ID INTEGER PRIMARY KEY, Name TEXT);";
                writeLog(createSchoolTable);
                // 创建班级表，其中SchoolID是外键，关联到学校表的ID字段
                string createClassTable = "CREATE TABLE Class (ID INTEGER PRIMARY KEY, Name TEXT, SchoolID INTEGER, FOREIGN KEY(SchoolID) REFERENCES School(ID));";
                writeLog(createClassTable);
                // 创建学生表，其中ClassID是外键，关联到班级表的ID字段
                string createStudentTable = "CREATE TABLE Student (ID INTEGER PRIMARY KEY, Name TEXT, Age INTEGER, ClassID INTEGER, FOREIGN KEY(ClassID) REFERENCES Class(ID));";
                writeLog(createStudentTable);
                SQLiteCommand createTableCommand = new SQLiteCommand(createSchoolTable + createClassTable + createStudentTable, connection);
                createTableCommand.ExecuteNonQuery();

                string createLogTable = "CREATE TABLE Log (ID INTEGER PRIMARY KEY, Operation TEXT, Timestamp DATETIME);";
                SQLiteCommand createLogTableCommand = new SQLiteCommand(createLogTable, connection);
                createLogTableCommand.ExecuteNonQuery();

                connection.Close();

            }
        }
        private void FillTreeView()
        {
            treeView1.Nodes.Clear();

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // 创建根节点
                TreeNode rootNode = new TreeNode("Schools");
                rootNode.Tag = "root"; // 根节点标记

                // 查询学校数据
                string schoolQuery = "SELECT * FROM School";
                using (SQLiteCommand schoolCmd = new SQLiteCommand(schoolQuery, connection))
                {
                    using (SQLiteDataReader schoolReader = schoolCmd.ExecuteReader())
                    {
                        while (schoolReader.Read())
                        {
                            int schoolID = schoolReader.GetInt32(0);
                            string schoolName = schoolReader.GetString(1);

                            // 创建学校节点
                            TreeNode schoolNode = new TreeNode(schoolName);
                            schoolNode.Tag = schoolID; // 学校ID作为节点标记

                            // 查询与学校相关的班级数据
                            string classQuery = "SELECT * FROM Class WHERE SchoolID = @SchoolID";
                            using (SQLiteCommand classCmd = new SQLiteCommand(classQuery, connection))
                            {
                                classCmd.Parameters.AddWithValue("@SchoolID", schoolID);

                                using (SQLiteDataReader classReader = classCmd.ExecuteReader())
                                {
                                    while (classReader.Read())
                                    {
                                        int classID = classReader.GetInt32(0);
                                        string className = classReader.GetString(1);

                                        // 创建班级节点
                                        TreeNode classNode = new TreeNode(className);
                                        classNode.Tag = classID; // 班级ID作为节点标记

                                        // 将班级节点添加到学校节点
                                        schoolNode.Nodes.Add(classNode);
                                    }
                                }
                            }

                            // 将学校节点添加到根节点
                            rootNode.Nodes.Add(schoolNode);
                        }
                    }
                }

                // 将根节点添加到TreeView
                treeView1.Nodes.Add(rootNode);

                connection.Close();
            }
        }
        public static void InsertStudent(SQLiteConnection connection, Student student)
        {
            string insertQuery = "INSERT INTO Student (ID, Name, Age, ClassID) VALUES (@ID, @Name, @Age, @ClassID)";
            using (SQLiteCommand command = new SQLiteCommand(insertQuery, connection))
            {
                try
                {
                    command.Parameters.AddWithValue("@ID", student.ID);
                    command.Parameters.AddWithValue("@Age", student.Age);
                    command.Parameters.AddWithValue("@Name", student.Name);
                    command.Parameters.AddWithValue("@ClassID", student.ClassID);

                    string fullSqlQuery = command.CommandText;
                    foreach (SQLiteParameter parameter in command.Parameters)
                    {
                        fullSqlQuery = fullSqlQuery.Replace(parameter.ParameterName, parameter.Value.ToString());
                    }
                    writeLog(fullSqlQuery);

                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {

                }
            }
        }

        public static void InsertSchool(SQLiteConnection connection, School school)
        {
            string insertQuery = "INSERT INTO School (ID, Name) VALUES (@ID, @Name)";
            using (SQLiteCommand command = new SQLiteCommand(insertQuery, connection))
            {
                try
                {
                    command.Parameters.AddWithValue("@ID", school.ID);
                    command.Parameters.AddWithValue("@Name", school.Name);

                    string fullSqlQuery = command.CommandText;
                    foreach (SQLiteParameter parameter in command.Parameters)
                    {
                        fullSqlQuery = fullSqlQuery.Replace(parameter.ParameterName, parameter.Value.ToString());
                    }
                    writeLog(fullSqlQuery);

                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    
                }
            }
        }

        public static void InsertClass(SQLiteConnection connection, Class schoolClass)
        {
            try
            {
                string insertQuery = "INSERT INTO Class (ID, Name, SchoolID) VALUES (@ID, @Name, @SchoolID)";
                using (SQLiteCommand command = new SQLiteCommand(insertQuery, connection))
                {
                    command.Parameters.AddWithValue("@ID", schoolClass.ID);
                    command.Parameters.AddWithValue("@Name", schoolClass.Name);
                    command.Parameters.AddWithValue("@SchoolID", schoolClass.SchoolID);

                    string fullSqlQuery = command.CommandText;
                    foreach (SQLiteParameter parameter in command.Parameters)
                    {
                        fullSqlQuery = fullSqlQuery.Replace(parameter.ParameterName, parameter.Value.ToString());
                    }
                    writeLog(fullSqlQuery);

                    command.ExecuteNonQuery();
                }
            }catch(Exception ex)
            {

            }
        }
        private void updateList()
        {
            listView1.Columns.Clear();
            if (chosed == SELECT.Student)
            {
                listView1.Columns.Add("ID", 150, HorizontalAlignment.Center);
                listView1.Columns.Add("姓名", 150, HorizontalAlignment.Center);
                listView1.Columns.Add("年龄", 150, HorizontalAlignment.Center);
                listView1.Columns.Add("班级ID", 150, HorizontalAlignment.Center);
                return;
            }
            if(chosed == SELECT.Class)
            {
                listView1.Columns.Add("ID", 150, HorizontalAlignment.Center);
                listView1.Columns.Add("班级名", 150, HorizontalAlignment.Center);
                listView1.Columns.Add("学校ID", 150, HorizontalAlignment.Center);
                return;
            }
            if (chosed == SELECT.School)
            {
                listView1.Columns.Add("ID", 150, HorizontalAlignment.Center);
                listView1.Columns.Add("学校名", 150, HorizontalAlignment.Center);
                return;
            }
        }
        private void listView1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void TreeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {
            switch (e.Node.Level)
            {
                case 0: chosed = SELECT.School;
                    showSchool();
                    break;
                case 1:chosed = SELECT.Class;
                    showClass(e.Node.Tag.ToString());
                    break;
                case 2: chosed = SELECT.Student;
                    showStudent(e.Node.Tag.ToString());
                    break;
                default: break;
            }
            
        }
        private void showSchool()
        {
            
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                listView1.Items.Clear();
                updateList();
                connection.Open();
                string classQuery = "SELECT * FROM School";
                using (SQLiteCommand Cmd = new SQLiteCommand(classQuery, connection))
                {

                    using (SQLiteDataReader Reader = Cmd.ExecuteReader())
                    {
                        while (Reader.Read())
                        {
                            int ID = Reader.GetInt32(0);
                            string name = Reader.GetString(1);
                            
                            ListViewItem item = new ListViewItem(ID.ToString());
                            item.SubItems.Add(name);

                            listView1.Items.Add(item);

                        }
                    }
                }
                connection.Close();
            }
        }
        private void showClass(string SchoolID)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                listView1.Items.Clear();
                updateList();
                connection.Open();
                string classQuery = "SELECT * FROM Class WHERE SchoolID = @SchoolID";
                using (SQLiteCommand Cmd = new SQLiteCommand(classQuery, connection))
                {
                    Cmd.Parameters.AddWithValue("@SchoolID", SchoolID);
                    using (SQLiteDataReader Reader = Cmd.ExecuteReader())
                    {
                        while (Reader.Read())
                        {
                            int ID = Reader.GetInt32(0);
                            string name = Reader.GetString(1);
                            int schoolID = Reader.GetInt32(2);
                            ListViewItem item = new ListViewItem(ID.ToString());
                            item.SubItems.Add(name);
                            item.SubItems.Add(schoolID.ToString());
                            listView1.Items.Add(item);

                        }
                    }
                }
                connection.Close();
            }
        }
        private void showStudent(string ClassID)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                listView1.Items.Clear();
                updateList();
                connection.Open();
                string classQuery = "SELECT * FROM Student WHERE ClassID = @ClassID";
                using (SQLiteCommand classCmd = new SQLiteCommand(classQuery, connection))
                {
                    classCmd.Parameters.AddWithValue("@ClassID", ClassID);

                    using (SQLiteDataReader studentReader = classCmd.ExecuteReader())
                    {
                        while (studentReader.Read())
                        {
                            int ID = studentReader.GetInt32(0);
                            string name = studentReader.GetString(1);
                            int age = studentReader.GetInt32(2);
                            int classID = studentReader.GetInt32(3);

                            ListViewItem item = new ListViewItem(ID.ToString());
                            item.SubItems.Add(name);
                            item.SubItems.Add(age.ToString());
                            item.SubItems.Add(classID.ToString());
                            listView1.Items.Add(item);

                        }
                    }
                }
                connection.Close();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Form2 f = new Form2(chosed,false);
            f.update += FillTreeView;
            f.Show();
        }


        public static void DeleteSchoolRecord(int schoolID, SQLiteConnection connection)
        {

            string deleteSchoolQuery = "DELETE FROM School WHERE ID = @SchoolID;";
            SQLiteCommand deleteSchoolCommand = new SQLiteCommand(deleteSchoolQuery, connection);
            deleteSchoolCommand.Parameters.AddWithValue("@SchoolID", schoolID);

            string fullSqlQuery = deleteSchoolCommand.CommandText;

            foreach (SQLiteParameter parameter in deleteSchoolCommand.Parameters)
            {
                fullSqlQuery = fullSqlQuery.Replace(parameter.ParameterName, parameter.Value.ToString());
            }
            writeLog(fullSqlQuery);

            deleteSchoolCommand.ExecuteNonQuery();

            connection.Close();
        }

        public static void DeleteClassRecord(int classID, SQLiteConnection connection)
        {
            string deleteClassQuery = "DELETE FROM Class WHERE ID = @ClassID;";
            SQLiteCommand deleteClassCommand = new SQLiteCommand(deleteClassQuery, connection);
            deleteClassCommand.Parameters.AddWithValue("@ClassID", classID);

            string fullSqlQuery = deleteClassCommand.CommandText;

            foreach (SQLiteParameter parameter in deleteClassCommand.Parameters)
            {
                fullSqlQuery = fullSqlQuery.Replace(parameter.ParameterName, parameter.Value.ToString());
            }
            writeLog(fullSqlQuery);

            deleteClassCommand.ExecuteNonQuery();
        }

        public static void DeleteStudentRecord(int studentID, SQLiteConnection connection)
        {

            string deleteStudentQuery = "DELETE FROM Student WHERE ID = @StudentID;";
            SQLiteCommand deleteStudentCommand = new SQLiteCommand(deleteStudentQuery, connection);
            deleteStudentCommand.Parameters.AddWithValue("@StudentID", studentID);

            string fullSqlQuery = deleteStudentCommand.CommandText;

            foreach (SQLiteParameter parameter in deleteStudentCommand.Parameters)
            {
                fullSqlQuery = fullSqlQuery.Replace(parameter.ParameterName, parameter.Value.ToString());
            }
            writeLog(fullSqlQuery);

            deleteStudentCommand.ExecuteNonQuery();

        }

        public static void UpdateSchool(School school, SQLiteConnection connection)
        {
            using (SQLiteCommand cmd = new SQLiteCommand(connection))
            {
                cmd.CommandText = "UPDATE School SET Name = @Name WHERE ID = @ID";
                cmd.Parameters.AddWithValue("@Name", school.Name);
                cmd.Parameters.AddWithValue("@ID", school.ID);

                string fullSqlQuery = cmd.CommandText;
                foreach (SQLiteParameter parameter in cmd.Parameters)
                {
                    fullSqlQuery = fullSqlQuery.Replace(parameter.ParameterName, parameter.Value.ToString());
                }
                writeLog(fullSqlQuery);

                cmd.ExecuteNonQuery();
            }
        }

        public static void UpdateClass(Class schoolClass, SQLiteConnection connection)
        {
            using (SQLiteCommand cmd = new SQLiteCommand(connection))
            {
                cmd.CommandText = "UPDATE Class SET Name = @Name, SchoolID = @SchoolID WHERE ID = @ID";
                cmd.Parameters.AddWithValue("@Name", schoolClass.Name);
                cmd.Parameters.AddWithValue("@SchoolID", schoolClass.SchoolID);
                cmd.Parameters.AddWithValue("@ID", schoolClass.ID);

                string fullSqlQuery = cmd.CommandText;
                foreach (SQLiteParameter parameter in cmd.Parameters)
                {
                    fullSqlQuery = fullSqlQuery.Replace(parameter.ParameterName, parameter.Value.ToString());
                }
                writeLog(fullSqlQuery);

                cmd.ExecuteNonQuery();
            }
        }

        public static void UpdateStudent(Student student, SQLiteConnection connection)
        {
            using (SQLiteCommand cmd = new SQLiteCommand(connection))
            {
                cmd.CommandText = "UPDATE Student SET Name = @Name, Age = @Age, ClassID = @ClassID WHERE ID = @ID";
                cmd.Parameters.AddWithValue("@Name", student.Name);
                cmd.Parameters.AddWithValue("@Age", student.Age);
                cmd.Parameters.AddWithValue("@ClassID", student.ClassID);
                cmd.Parameters.AddWithValue("@ID", student.ID);

                string fullSqlQuery = cmd.CommandText;
                foreach (SQLiteParameter parameter in cmd.Parameters)
                {
                    fullSqlQuery = fullSqlQuery.Replace(parameter.ParameterName, parameter.Value.ToString());
                }
                writeLog(fullSqlQuery);

                cmd.ExecuteNonQuery();
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            Form2 f = new Form2(chosed, true);
            f.update += FillTreeView;
            f.Show();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Form3 f = new Form3(chosed,true);
            f.update += FillTreeView;
            f.Show();
        }

        public static string FindSchoolByID(int schoolID, SQLiteConnection connection)
        {
            using (SQLiteCommand cmd = new SQLiteCommand(connection))
            {
                cmd.CommandText = @"SELECT School.Name AS SchoolName
                            FROM School
                            WHERE School.ID = @SchoolID";
                cmd.Parameters.AddWithValue("@SchoolID", schoolID);

                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        string schoolName = reader.GetString(0);

                        return schoolName;
                    }
                }
            }

            return "无信息"; 
        }

        public static string FindClassByID(int classID, SQLiteConnection connection)
        {
            using (SQLiteCommand cmd = new SQLiteCommand(connection))
            {
                cmd.CommandText = @"SELECT Class.Name AS ClassName, School.Name AS SchoolName
                            FROM Class
                            JOIN School ON Class.SchoolID = School.ID
                            WHERE Class.ID = @ClassID";
                cmd.Parameters.AddWithValue("@ClassID", classID);

                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        string className = reader.GetString(0);
                        string schoolName = reader.GetString(1);

                        return $"{className} - {className}";
                    }
                }
            }

            return "无信息"; 
        }


        public static string FindStudentByID(int studentID, SQLiteConnection connection)
        {
            using (SQLiteCommand cmd = new SQLiteCommand(connection))
            {
                cmd.CommandText = @"SELECT Student.Name, Class.Name AS ClassName, School.Name AS SchoolName
                            FROM Student
                            JOIN Class ON Student.ClassID = Class.ID
                            JOIN School ON Class.SchoolID = School.ID
                            WHERE Student.ID = @StudentID";
                cmd.Parameters.AddWithValue("@StudentID", studentID);

                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        string studentName = reader.GetString(0);
                        string className = reader.GetString(1);
                        string schoolName = reader.GetString(2);

                        return $"{schoolName} - {className} - {studentName}";
                    }
                }
            }

            return "无信息"; 
        }

        private void button4_Click(object sender, EventArgs e)
        {
            Form3 f = new Form3(chosed, false);
            f.Show();
        }

        private void button5_Click(object sender, EventArgs e)
        {
            try
            {
                Process.Start("log.txt");
            }
            catch (Exception )
            {
                
            }
        }
    }
}
